/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package proyectodbtienda;

import javax.swing.JOptionPane;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableColumn;
/**
 *
 * @author Shaka
 */
public class Tables extends javax.swing.JFrame {
public int indx;
private Connection conn;
private Statement stmt;
private ResultSet rst;
private Connection connr;
private Statement stmtr;
private ResultSet rstr;
private DefaultTableModel modelo;
public int iduser;
    /** Creates new form Tables */
public Tables(){        
        try {
            initComponents();
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // 2005 version 1433
            conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1051;databaseName=tienda;user=sa;password=ecliptica;");//iti
            //conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1055;databaseName=test;user=sa;password=fernanda;");//fer
            stmt = conn.createStatement();
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // 2005 version 1433
            connr = DriverManager.getConnection("jdbc:sqlserver://tecnoserver;databaseName=002;user=sa;password=Abc1234.;");//iti
            //conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1055;databaseName=test;user=sa;password=fernanda;");//fer
            stmtr = connr.createStatement();

        } catch ( SQLException excepcionSql){ //excepcionSql = puede ponerle otro nombre
            JOptionPane.showMessageDialog( null, excepcionSql.getMessage(),
            "Error en base de datos", JOptionPane.ERROR_MESSAGE );
            }
            //errores con la carga del controlador de la base de datos(Driver)
            catch ( ClassNotFoundException claseNoEncontrada ){
            JOptionPane.showMessageDialog( null, claseNoEncontrada.getMessage(),
            "No se encontró el controlador", JOptionPane.ERROR_MESSAGE );
            }
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jPanel1 = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        jComboBox1 = new javax.swing.JComboBox();
        jButton3 = new javax.swing.JButton();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jPanel2 = new javax.swing.JPanel();
        jButton1 = new javax.swing.JButton();
        jButton2 = new javax.swing.JButton();
        jButton4 = new javax.swing.JButton();

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Seleccione La tabla a Mostrar"));

        jLabel1.setFont(new java.awt.Font("Tahoma", 1, 14));
        jLabel1.setText("Tabla:");
        jPanel1.add(jLabel1);

        jComboBox1.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Ciudades", "Clientes", "Compras", "Detalle de Compras", "Detalle de Venta", "Estados", "Productos", "Proveedores", "Ventas", "Clientes Remotos" }));
        jPanel1.add(jComboBox1);

        jButton3.setText("Mostrar");
        jButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton3ActionPerformed(evt);
            }
        });
        jPanel1.add(jButton3);

        getContentPane().add(jPanel1, java.awt.BorderLayout.NORTH);

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null},
                {null, null, null, null}
            },
            new String [] {
                "Title 1", "Title 2", "Title 3", "Title 4"
            }
        ));
        jScrollPane1.setViewportView(jTable1);

        getContentPane().add(jScrollPane1, java.awt.BorderLayout.CENTER);

        jButton1.setText("Agregar");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });
        jPanel2.add(jButton1);

        jButton2.setText("Eliminar");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });
        jPanel2.add(jButton2);

        jButton4.setText("Cambiar Password");
        jButton4.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton4ActionPerformed(evt);
            }
        });
        jPanel2.add(jButton4);

        getContentPane().add(jPanel2, java.awt.BorderLayout.PAGE_END);

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
         // TODO add your handling code here:
        indx=jComboBox1.getSelectedIndex();
        Object ob[];
        DefaultTableModel model;
        try {
        switch(indx){
        case 0:
                ob = new Object[3];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDCiudad");
                model.addColumn("Nombre");
                model.addColumn("IDEstado");
                modelo = model;

            rst=stmt.executeQuery("select * from Ciudades");
            while(rst.next()){
                ob[0] =rst.getInt("IdCiudad");
                ob[1] = rst.getString("nombreCiudad");
                ob[2] = rst.getString("IdEstado");
                modelo.addRow(ob);
            }
            break;
            case 1:
                ob = new Object[7];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDCliente");
                model.addColumn("Nombre");
                model.addColumn("RFC");
                model.addColumn("Teléfono");
                model.addColumn("Dirección");                             
                model.addColumn("IDCiudad");                
                model.addColumn("Activo");
                modelo = model;

           rst=stmt.executeQuery("select * from clientes where activo = 's' or activo ='S'");
            
            while(rst.next()){
                ob[0] =rst.getInt("idcliente");
                ob[1] = rst.getString("nombre");
                ob[2] = rst.getString("rfc");
                ob[3] = rst.getString("telefono");
                ob[4] = rst.getString("direccion");
                ob[5] = rst.getString("idCiudad");
                ob[6] = rst.getString("activo");              
                modelo.addRow(ob);
            }
        break;
        case 2:
                ob = new Object[4];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDCompras");
                model.addColumn("Importe");
                model.addColumn("IDProveedor");
                model.addColumn("Fecha de Compra");
                modelo = model;

            rst=stmt.executeQuery("select * from compras");
            while(rst.next()){
                ob[0] =rst.getInt("idcompra");
                ob[1] = rst.getString("importe");
                ob[2] = rst.getString("idproveedor");
                ob[3] = rst.getString("fechacompra");
                modelo.addRow(ob);
            }
        break;
        case 3:
                ob = new Object[5];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDDetalle");
                model.addColumn("IDCompra");
                model.addColumn("IDProducto");
                model.addColumn("Cantidad");
                model.addColumn("Importe");
                modelo = model;

            rst=stmt.executeQuery("select * from detallecompras");
            while(rst.next()){
                ob[0] =rst.getInt("idDcompras");
                ob[1] = rst.getString("idcompra");
                ob[2] = rst.getString("idproducto");
                ob[3] = rst.getString("cantidad");
                ob[4] = rst.getString("importe");
                modelo.addRow(ob);
            }
        break;
        case 4:
                ob = new Object[5];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDDetalle");
                model.addColumn("IDVenta");
                model.addColumn("IDProducto");
                model.addColumn("Cantidad");
                model.addColumn("Importe");
                modelo = model;

            rst=stmt.executeQuery("select * from detalleVenta");
            while(rst.next()){
                ob[0] =rst.getInt("idDventa");
                ob[1] = rst.getString("idventa");
                ob[2] = rst.getString("idproducto");
                ob[3] = rst.getString("cantidad");
                ob[4] = rst.getString("importe");
                modelo.addRow(ob);
            }
        break;
        case 5:
                ob = new Object[2];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDEstado");
                model.addColumn("Nombre Estado");
                modelo = model;

            rst=stmt.executeQuery("select * from Estados");
            while(rst.next()){
                ob[0] =rst.getInt("idEstado");
                ob[1] = rst.getString("nombreEstado");
                modelo.addRow(ob);
            }
        break;
        case 6:
                ob = new Object[6];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDProducto");
                model.addColumn("Nombre");
                model.addColumn("Precio");
                model.addColumn("Existencia");
                model.addColumn("IDProveedor");
                model.addColumn("Activo");
                modelo = model;

            rst=stmt.executeQuery("select * from productos where activo = 's' or activo ='S'");
            while(rst.next()){
                ob[0] =rst.getInt("idproducto");
                ob[1] = rst.getString("nombre");
                ob[2] = rst.getString("precio");
                ob[3] = rst.getString("existencia");
                ob[4] = rst.getString("idproveedor");
                ob[5] = rst.getString("activo");
                modelo.addRow(ob);
            }
        break;
        case 7:
                ob = new Object[5];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDProveedor");
                model.addColumn("RFC");
                model.addColumn("Nombre");
                model.addColumn("Dirección");
                model.addColumn("Activo");
                modelo = model;

            rst=stmt.executeQuery("select * from proveedores where activo = 's' or activo ='S'");
            while(rst.next()){
                ob[0] =rst.getInt("idproveedor");
                ob[1] = rst.getString("RFC");
                ob[2] = rst.getString("Nombre");
                ob[3] = rst.getString("direccion");
                ob[4] = rst.getString("activo");
                modelo.addRow(ob);
            }
        break;
        case 8:
                ob = new Object[4];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDVenta");
                model.addColumn("IDCliente");
                model.addColumn("Fecha de Venta");
                model.addColumn("Total");
                modelo = model;

            rst=stmt.executeQuery("select * from ventas");
            while(rst.next()){
                ob[0] =rst.getInt("idventa");
                ob[1] = rst.getString("idcliente");
                ob[2] = rst.getString("fechaVenta");
                ob[3] = rst.getString("total");
                modelo.addRow(ob);
            }
        break;
            case 9:
                ob = new Object[7];
                model = new DefaultTableModel();
                jTable1.setModel(model);
                model.addColumn("IDCliente");
                model.addColumn("Nombre");
                model.addColumn("Dirección");
                model.addColumn("Teléfono");
                model.addColumn("IDCiudad");
                model.addColumn("RFC");
                model.addColumn("Activo");
                modelo = model;

                 rstr=stmtr.executeQuery("select * from clientes where activo=1");
            while(rstr.next()){

                ob[0] =rstr.getInt("idcliente");
                ob[1] = rstr.getString("nombre");
                ob[2] = rstr.getString("direccion");
                ob[3] = rstr.getString("telefono");
                ob[4] = rstr.getString("idciudad");
                ob[5] = rstr.getString("rfc");
                ob[6] = rstr.getInt("activo");
                modelo.addRow(ob);}
                break;
      }
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_jButton3ActionPerformed

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        // TODO add your handling code here:        
        System.out.println(indx);
        AddData ad = new AddData(indx);
       ad.setVisible(true);
        
    }//GEN-LAST:event_jButton1ActionPerformed
   
    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        // TODO add your handling code here:
        String Sindx="";
             int h;
      try{
          switch(indx)
          {
              case 1:
                  h = jTable1.getSelectedRow();
                  Sindx +=  jTable1.getValueAt(h,0);
                  stmt.executeUpdate("update clientes set activo ='n' where idcliente="+Sindx);
                  JOptionPane.showMessageDialog(null, "cliente pasó a no activo");
                  if (jTable1 != null) {
                        while (jTable1.getColumnCount() > 0) {
                            TableColumn col = jTable1.getColumnModel().getColumn(0);
                            jTable1.removeColumn(col);
                        }
                    }
                    jButton3ActionPerformed(evt);
              break;
              case 6:
                  h = jTable1.getSelectedRow();
                  Sindx +=  jTable1.getValueAt(h,0);
                  stmt.executeUpdate("update productos set activo ='n' where idproducto="+Sindx);
                  JOptionPane.showMessageDialog(null, "producto pasó a no activo");
                  if (jTable1 != null) {
                        while (jTable1.getColumnCount() > 0) {
                            TableColumn col = jTable1.getColumnModel().getColumn(0);
                            jTable1.removeColumn(col);
                        }
                    }
                    jButton3ActionPerformed(evt);
                  break;
               case 7:
                  h = jTable1.getSelectedRow();
                  Sindx +=  jTable1.getValueAt(h,0);
                  stmt.executeUpdate("update proveedores set activo ='n' where idproveedor="+Sindx);
                  JOptionPane.showMessageDialog(null, "proveedor pasó a no activo");
                  if (jTable1 != null) {
                        while (jTable1.getColumnCount() > 0) {
                            TableColumn col = jTable1.getColumnModel().getColumn(0);
                            jTable1.removeColumn(col);
                        }
                    }
                    jButton3ActionPerformed(evt);
                  break;
              case 9:
                  h = jTable1.getSelectedRow();
                  Sindx +=  jTable1.getValueAt(h,0);
                  stmtr.executeUpdate("update clientes set activo = 0 where idcliente="+Sindx);
                  JOptionPane.showMessageDialog(null, "cliente pasó a no activo");
                  if (jTable1 != null) {
                        while (jTable1.getColumnCount() > 0) {
                            TableColumn col = jTable1.getColumnModel().getColumn(0);
                            jTable1.removeColumn(col);
                        }
                    }
                    jButton3ActionPerformed(evt);
                  break;
          }

      } catch (SQLException ex) {
            Logger.getLogger(AddData.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_jButton2ActionPerformed

    private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed
        // TODO add your handling code here:
        ModPass mp = new ModPass();
        mp.id=iduser;
        mp.setVisible(true);
    }//GEN-LAST:event_jButton4ActionPerformed

    /**
    * @param args the command line arguments
    */
     public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new Tables().setVisible(true);
            }
        });
    }


    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private javax.swing.JButton jButton3;
    private javax.swing.JButton jButton4;
    private javax.swing.JComboBox jComboBox1;
    public javax.swing.JLabel jLabel1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JScrollPane jScrollPane1;
    public javax.swing.JTable jTable1;
    // End of variables declaration//GEN-END:variables

}
